﻿Public Class filtroBuscar

    Dim cadena As String = "Data Source=CESAR-PC\SQLSERVER;Initial Catalog=CAPCrepuestos;Integrated Security=True"

    Private Sub btnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscar.Click
        If txtNumero.Text = "" Then txtNumero.Text = 0
        cargarGrilla(buscarCliente(txtNumero.Text, txtNombre.Text, txtRazonSocial.Text, txtCUIT.Text))
        txtNumero.Text = ""
        btnSeleccionar.Enabled = (grilla.RowCount > 0)
    End Sub

    Private Function buscarCliente(ByVal id As Integer, ByVal nombre As String, ByVal razon_social As String, ByVal CUIT As String) As Data.DataTable
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        If CUIT = "  -        -" Then CUIT = ""

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion

        If id = 0 Then
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT id as 'Nro Cliente', nombre as Nombre, razon_social as 'Razon Social', CUIT, direccion, barrio, localidad, telefono, transporte, contacto_nombre, contacto_apellido, celular FROM clientes"
            cmd.CommandText &= " WHERE nombre LIKE '%" & nombre & "%' AND razon_social LIKE '%" & razon_social & "%' AND CUIT LIKE '%" & CUIT & "%'"
            tabla.Load(cmd.ExecuteReader)

        Else
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT id as 'Nro Cliente', nombre as Nombre, razon_social as 'Razon Social', CUIT, direccion, barrio, localidad, telefono, transporte, contacto_nombre, contacto_apellido, celular FROM clientes"
            cmd.CommandText &= " WHERE id =" & id & " AND nombre LIKE '%" & nombre & "%' AND razon_social LIKE '%" & razon_social & "%' AND CUIT LIKE '%" & CUIT & "%'"
            tabla.Load(cmd.ExecuteReader)
        End If
        conexion.Close()
        Return tabla

    End Function

    Private Sub filtroBuscar_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        btnSeleccionar.Enabled = False


    End Sub

    Private Sub cargarGrilla(ByVal tabla As Data.DataTable)
        Me.grilla.DataSource = tabla
        Me.grilla.Columns(0).HeaderText = "Nro Cliente"
        Me.grilla.Columns(0).Width = 100
        Me.grilla.Columns(1).HeaderText = "Nombre"
        Me.grilla.Columns(1).Width = 100
        Me.grilla.Columns(2).HeaderText = "Razon Social"
        Me.grilla.Columns(2).Width = 100
        Me.grilla.Columns(3).HeaderText = "CUIT"
        Me.grilla.Columns(3).Width = 100
        Me.grilla.Columns(4).HeaderText = "Direccion"
        Me.grilla.Columns(4).Width = 100
        Me.grilla.Columns(5).HeaderText = "Barrio"
        Me.grilla.Columns(5).Width = 100
        Me.grilla.Columns(6).HeaderText = "Localidad"
        Me.grilla.Columns(6).Width = 100
        Me.grilla.Columns(7).HeaderText = "Telefono"
        Me.grilla.Columns(7).Width = 100
        Me.grilla.Columns(8).HeaderText = "Transporte"
        Me.grilla.Columns(8).Width = 100
        Me.grilla.Columns(9).HeaderText = "Nombre Contacto"
        Me.grilla.Columns(9).Width = 100
        Me.grilla.Columns(10).HeaderText = "Apellido Contacto"
        Me.grilla.Columns(10).Width = 100
        Me.grilla.Columns(11).HeaderText = "Celular"
        Me.grilla.Columns(11).Width = 100
    End Sub

    Private Sub btnSeleccionar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSeleccionar.Click

        clientes.txtNroCliente.Text = grilla.CurrentRow.Cells(0).Value
        clientes.cmbNombreCliente.Text = grilla.CurrentRow.Cells(1).Value
        clientes.txtRazonSocial.Text = grilla.CurrentRow.Cells(2).Value
        clientes.txtCUIT.Text = grilla.CurrentRow.Cells(3).Value
        clientes.txtDireccion.Text = grilla.CurrentRow.Cells(4).Value
        clientes.txtBarrio.Text = grilla.CurrentRow.Cells(5).Value
        clientes.txtLocalidad.Text = grilla.CurrentRow.Cells(6).Value
        clientes.txtTelefono.Text = grilla.CurrentRow.Cells(7).Value
        clientes.txtTransporte.Text = grilla.CurrentRow.Cells(8).Value
        clientes.txtNombreContacto.Text = grilla.CurrentRow.Cells(9).Value
        clientes.txtApellidoContacto.Text = grilla.CurrentRow.Cells(10).Value
        clientes.txtCelular.Text = grilla.CurrentRow.Cells(11).Value

        Me.Close()
    End Sub
End Class